explore

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(wesanderson)
library(purrr)
library(xml2)
library(tidyverse)
## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2
## ──
## ✔ ggplot2 3.4.0     ✔ readr   2.1.3
## ✔ tibble  3.1.8     ✔ stringr 1.5.0
## ✔ tidyr   1.3.0     ✔ forcats 0.5.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
library(ggplot2)
library(kableExtra)
## 
## Attaching package: 'kableExtra'
## 
## The following object is masked from 'package:dplyr':
## 
##     group_rows
library(plotly)
## 
## Attaching package: 'plotly'
## 
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## 
## The following object is masked from 'package:stats':
## 
##     filter
## 
## The following object is masked from 'package:graphics':
## 
##     layout
library(shiny)
################################################################################
# extract variables in the IRS990ScheduleD node from the given file
################################################################################
# output is a dataframe with the filename and name of each variable extracted

collect_sched_d_vars <- function(input_file) {
  example_file <- xml2::read_xml(input_file)%>% xml_ns_strip()

  # look at all variable names in schedule D
  IRS990ScheduleD_vars <- example_file %>% 
    xml_find_all("//ReturnData//IRS990ScheduleD")  %>%
    xml_contents() %>% 
    xml_path() 
  
  # look at variable names of children nodes of the schedule D nodes
  IRS990ScheduleD_var_children <- IRS990ScheduleD_vars %>% 
    map(~xml_find_all(example_file,
                      xpath = gsub("/", 
                                   "//" ,
                                   .x, 
                                   fixed = TRUE) ) %>%
          xml_children() %>% 
          xml_path()) %>%
    unlist()
  
    # include both first level schedule D nodes and children nodes
    vars <- c(IRS990ScheduleD_vars, IRS990ScheduleD_var_children)
    
    tibble(variables = vars,
           filename = input_file)
}

# file paths in the directory
files <- dir( "/Users/niezhen/Desktop/Spring 2023/SDS 410/ballet_990_released_20230208",
               full.names = TRUE)

# iterate over all files and extract variables present
# output is a data frame with a file name column and variable name column
all_vars <- map_df(files, collect_sched_d_vars)

all_vars

Buildings Grouop Explanation

Copied from https://www.irs.gov/pub/irs-pdf/i990sd.pdf

Part VI. Land, Buildings, and Equipment Complete Part VI if the organization answered “Yes” on Form 990, Part IV, line 11a, and reported an amount on Form 990, Part X, line 10a. Reporting is required if any amount other than zero is reported on those lines.

Column (a). Enter the cost or other basis of all land, buildings, leasehold improvements, equipment, and other fixed assets held for investment purposes, such as rental properties.

Column (b). Enter the cost or other basis of all other land, buildings, leasehold improvements, equipment, and other fixed assets held for other than investment purposes, including any land, buildings, and equipment owned and used by the organization in conducting its exempt activities. The total amounts reported in columns (a) and (b) must equal the amount reported on Form 990, Part X, line 10a.

Column (c). Enter the accumulated depreciation recorded for the assets listed in columns (a) and (b). Don’t enter an amount in column (c) for line 1a, Land. The total of column (c) must equal the amount reported on Form 990, Part X, line 10b.

Column (d). Enter the sum of column (a) and column (b) minus column (c).

##From Rose's get_endowment function
get_endowment <- function(filename) {
  
  # Retrieving the same endowment information for all 
  variables <- c("//Return//ReturnHeader//ReturnTs", 
                 "//Return//ReturnHeader//Filer//EIN", 
                 "//Return//ReturnData//IRS990//DonorRstrOrQuasiEndowmentsInd",
                 "//Return//ReturnData//IRS990ScheduleD//CYEndwmtFundGrp//BeginningYearBalanceAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYEndwmtFundGrp//ContributionsAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYEndwmtFundGrp//InvestmentEarningsOrLossesAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYEndwmtFundGrp//OtherExpendituresAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYEndwmtFundGrp//EndYearBalanceAmt",
                 
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus1YrEndwmtFundGrp//BeginningYearBalanceAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus1YrEndwmtFundGrp//ContributionsAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus1YrEndwmtFundGrp//InvestmentEarningsOrLossesAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus1YrEndwmtFundGrp//OtherExpendituresAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus1YrEndwmtFundGrp//EndYearBalanceAmt",
                
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus2YrEndwmtFundGrp//BeginningYearBalanceAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus2YrEndwmtFundGrp//ContributionsAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus2YrEndwmtFundGrp//InvestmentEarningsOrLossesAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus2YrEndwmtFundGrp//OtherExpendituresAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus2YrEndwmtFundGrp//EndYearBalanceAmt",
                 
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus3YrEndwmtFundGrp//BeginningYearBalanceAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus3YrEndwmtFundGrp//ContributionsAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus3YrEndwmtFundGrp//InvestmentEarningsOrLossesAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus3YrEndwmtFundGrp//OtherExpendituresAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus3YrEndwmtFundGrp//EndYearBalanceAmt",
                 
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus4YrEndwmtFundGrp//BeginningYearBalanceAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus4YrEndwmtFundGrp//ContributionsAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus4YrEndwmtFundGrp//InvestmentEarningsOrLossesAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus4YrEndwmtFundGrp//OtherExpendituresAmt",
                 "//Return//ReturnData//IRS990ScheduleD//CYMinus4YrEndwmtFundGrp//EndYearBalanceAmt",
                 
                 "//Return//ReturnData//IRS990ScheduleD//BoardDesignatedBalanceEOYPct",
                 "//Return//ReturnData//IRS990ScheduleD//PrmnntEndowmentBalanceEOYPct",
                 "//Return//ReturnData//IRS990ScheduleD//TermEndowmentBalanceEOYPct",
                 "//Return//ReturnData//IRS990ScheduleD//EndowmentsHeldUnrelatedOrgInd",
                 "//Return//ReturnData//IRS990ScheduleD//EndowmentsHeldRelatedOrgInd",
                 
                 # adding BuildingGrp and OtherLandBuildingsGrp
                 "/Return/ReturnData/IRS990ScheduleD/OtherLandBuildingsGrp/OtherCostOrOtherBasisAmt",     
                 "/Return/ReturnData/IRS990ScheduleD/OtherLandBuildingsGrp/DepreciationAmt",             
                 "/Return/ReturnData/IRS990ScheduleD/OtherLandBuildingsGrp/BookValueAmt",                 
                 "/Return/ReturnData/IRS990ScheduleD/BuildingsGrp/OtherCostOrOtherBasisAmt",              
                 "/Return/ReturnData/IRS990ScheduleD/BuildingsGrp/DepreciationAmt",                       
                 "/Return/ReturnData/IRS990ScheduleD/BuildingsGrp/BookValueAmt",                          
                 "/Return/ReturnData/IRS990ScheduleD/OtherLandBuildingsGrp/InvestmentCostOrOtherBasisAmt",
                  "/Return/ReturnData/IRS990ScheduleD/BuildingsGrp/InvestmentCostOrOtherBasisAmt",         
                 
                 "//AmendedReturnInd",
                 "//Return//ReturnHeader//ReturnTypeCd"
                 )
  
  # Column name; order matters, needs to align with retrieval order
  variables_no_path <- c("ReturnTs", 
                         "EIN",
                         "DonorRstrOrQuasiEndowmentsInd",
                        "CYBeginningYearBalanceAmt", 
                        "CYContributionsAmt", 
                        "CYInvestmentEarningsOrLossesAmt",
                        "CYOtherExpendituresAmt",
                        "CYEndYearBalanceAmt",
                        
                        "CYM1BeginningYearBalanceAmt", 
                        "CYM1ContributionsAmt", 
                        "CYM1InvestmentEarningsOrLossesAmt",
                        "CYM1OtherExpendituresAmt",
                        "CYM1EndYearBalanceAmt",
                        
                        "CYM2BeginningYearBalanceAmt", 
                        "CYM2ContributionsAmt", 
                        "CYM2InvestmentEarningsOrLossesAmt",
                        "CYM2OtherExpendituresAmt",
                        "CYM2EndYearBalanceAmt",
                        
                        "CYM3BeginningYearBalanceAmt", 
                        "CYM3ContributionsAmt", 
                        "CYM3InvestmentEarningsOrLossesAmt",
                        "CYM3OtherExpendituresAmt",
                        "CYM3EndYearBalanceAmt",
                        
                        "CYM4BeginningYearBalanceAmt", 
                        "CYM4ContributionsAmt", 
                        "CYM4InvestmentEarningsOrLossesAmt",
                        "CYM4OtherExpendituresAmt",
                        "CYM4EndYearBalanceAmt",
                        
                        "BoardDesignatedBalanceEOYPct",
                        "PrmnntEndowmentBalanceEOYPct",
                        "TermEndowmentBalanceEOYPct",
                        "EndowmentsHeldUnrelatedOrgInd",
                        "EndowmentsHeldRelatedOrgInd",
                        
                         "OtherLandBuildingsGrp_OtherCostOrOtherBasisAmt",     
                         "OtherLandBuildingsGrp_DepreciationAmt",             
                         "OtherLandBuildingsGrp_BookValueAmt",                 
                         "BuildingsGrp_OtherCostOrOtherBasisAmt",              
                         "BuildingsGrp_DepreciationAmt",                       
                         "BuildingsGrp_BookValueAmt",                          
                         "OtherLandBuildingsGrp_InvestmentCostOrOtherBasisAmt",
                        "BuildingsGrp_InvestmentCostOrOtherBasisAmt",         
                        
                        "AmendedReturnInd",
                        "ReturnTypeCd"
                        )
  
  xml_file <- read_xml(filename)
  xml_file <- xml_ns_strip(xml_file)
  
  # extract each variable; if it isn't present, put NA 
  extracted <- map(variables, ~{
    value <- xml_find_all(
      xml_file, 
      xpath =.x)
    value <- ifelse(length(value) ==0, 
                    NA, 
                    xml_text(value)) })
  
   names(extracted) <- variables_no_path
   
   extracted <- extracted %>%
     as_tibble()
}
##Applying get_endowment to entire output 
files <- dir( "/Users/niezhen/Desktop/Spring 2023/SDS 410/ballet_990_released_20230208",
              full.names = TRUE)

endowment_data_building <- map_df(files, ~
                     get_endowment(.x)) 

all_data<-readRDS("./data/data_990.RDS")
filter_data <- all_data %>%
  filter(ReturnTypeCd == "990" | ReturnTypeCd == "990EZ") %>%
  mutate(amended_return_filled = ifelse(!is.na(AmendedReturnInd),1, 0)) %>%
  group_by(EIN, fiscal_year) %>%
  slice_max(order_by =amended_return_filled, n = 1) %>%
  ungroup()
##Retriving Ts and EINs for filtered data 
filter_ids <- filter_data %>%
  select(ReturnTs, EIN)
## Adjusting data type, filtering to proper 990s
endowment_data_building <- endowment_data_building %>%
  mutate(ReturnDate = as.Date(ReturnTs,
                              format = "%Y-%m-%d")) %>%
  mutate(across(CYBeginningYearBalanceAmt:TermEndowmentBalanceEOYPct,
                as.numeric)) %>%
  mutate(across(c(EndowmentsHeldRelatedOrgInd, EndowmentsHeldUnrelatedOrgInd, DonorRstrOrQuasiEndowmentsInd),
                ~ifelse(.x == "true" | .x == "1", TRUE, FALSE))) %>%
  
  filter(ReturnTypeCd == "990" | ReturnTypeCd == "990EZ") %>%
  right_join(filter_ids, by = c("ReturnTs", "EIN")) %>%
  select(-c(ReturnTypeCd,AmendedReturnInd)) #Removing columns needed for filtering

saveRDS(endowment_data_building, "./data/endowment_filtered_buildings.RDS")

saveRDS(all_data_filtered, "./data/all_data_filtered.RDS")

Preliminary Analysis of PartIV Data

Term explained: Book value is an accounting term used for both a measure of a business’s equity and the value of an asset as it appears on a balance sheet. As time goes on, the cost stays the same, but the accumulated depreciation increases, so the book value decreases.

# filter out all data each year
data_each_year<-list(
  data_2015 <- filter(endowment_data_building, grepl("2015", ReturnTs)),
  data_2016 <- filter(endowment_data_building, grepl("2016", ReturnTs)),
  data_2017 <- filter(endowment_data_building, grepl("2017", ReturnTs)),
  data_2018 <- filter(endowment_data_building, grepl("2018", ReturnTs)),
  data_2019 <- filter(endowment_data_building, grepl("2019", ReturnTs)),
  data_2020 <- filter(endowment_data_building, grepl("2020", ReturnTs)),
  data_2021 <- filter(endowment_data_building, grepl("2021", ReturnTs)),
  data_2022 <- filter(endowment_data_building, grepl("2022", ReturnTs))
)
BookValueSum <- list()

# restore each year's sum for each EIN's buildings bookvalue 
for(i in c(1:8)){
  data = data_each_year[[i]]
  df <- data.frame(data %>% 
    filter(!is.na(BuildingsGrp_BookValueAmt)) %>% 
    mutate_at(c("BuildingsGrp_BookValueAmt", "BuildingsGrp_DepreciationAmt"), as.numeric) %>% 
    group_by(EIN) %>% 
    summarise(
      BookValueSum = sum(BuildingsGrp_BookValueAmt)
    ) %>% 
    mutate(year = 2014+i,
           EIN = as.numeric(EIN)))
           
  #print(df)
  BookValueSum[[i]] = df
}
#  convert the list into dataframe
bookvalues <- do.call(rbind, BookValueSum)
# rank book value based on quantile
bookvalues<-bookvalues %>%
  mutate(quantile = ntile(BookValueSum, 10),
         EIN = as.character(EIN)) %>% 
  arrange(desc(EIN)) %>% 
  inner_join(y = ein_to_name, by = "EIN") 
#dataframe with higher Book Values
high <- bookvalues %>% 
  filter(year == 2020) %>% 
  filter(quantile >= 8) %>% 
  select(EIN) %>% 
  inner_join(bookvalues, by = "EIN") %>% 
  select(c(1,2,3,4,5))

medium <- bookvalues %>% 
  filter(year == 2020) %>% 
  filter(quantile > 3 & quantile < 7) %>% 
  select(EIN) %>% 
  inner_join(bookvalues, by = "EIN") %>% 
  select(c(1,2,3,4,5))
  
low <- bookvalues %>% 
  filter(year == 2020) %>% 
  filter(quantile <= 3) %>% 
  select(EIN) %>% 
  inner_join(bookvalues, by = "EIN") %>% 
  select(c(1,2,3,4,5))
# visualization of overall book value
total <- ggplot(bookvalues, aes(x = year, y = BookValueSum, color = as.factor(BusinessName))) +
  geom_line()+
  #\scale_color_brewer(palette="BrBG")+
  theme_bw()+
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) +
  labs(y = "Schedule D Sum of Buildings Book Value ($)",
       x = "Year",
       title = "Schedule D Sum of Buildings Book Value Change",
       subtitle = "By Fiscal Year")
ggplotly(total)
# separate EIN with medium and low building book value according to previous graph
# high
options(scipen = 999)
  high<-ggplot(high, aes(x = year, y = BookValueSum, color = as.factor(BusinessName))) +
  geom_line() +
  theme_bw() +
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 10, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10),
        strip.text = element_text(face="bold",size = 5)) +
  labs(y = "Schedule D Sum of Buildings Book Value",
       x = "Year",
       title = "Schedule D Sum of Buildings Book Value Change (High)",
       subtitle = "By Fiscal Year")

ggplotly(high)
# medium
options(scipen = 999)
medium <- ggplot(medium, aes(x = year, y = BookValueSum, color = as.factor(BusinessName))) +
  geom_line()+
  theme_bw() +
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 8, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 7),
        strip.text = element_text(face="bold",size = 5)) +
  labs(y = "Schedule D Sum of Buildings Book Value",
       x = "Year",
       title = "Schedule D Sum of Buildings Book Value Change (Medium)",
       subtitle = "By Fiscal Year")

ggplotly(medium)
# low
  ggplot(low, aes(x = year, y = BookValueSum, color = as.factor(BusinessName))) +
  geom_line()+
  theme_bw() +
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 10, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10),
        strip.text = element_text(face="bold",size = 5)) +
  labs(y = "Schedule D Sum of Buildings Book Value",
       x = "Year",
       title = "Schedule D Sum of Buildings Book Value Change (Low)",
       subtitle = "By Fiscal Year")

The book value will decrease because of depreciation, so the normal trend of an EIN from previous visualization should be a slight downward line. I will filter out EIN with a bizarre trend that should be : 1) Abruptly going down 2) going up at any point 3) appear less than 3 years

Caveat: ways of splitting up split up by quantile add geom_point

Unusual EIN’s

# rank book value based on quantile
EIN_appearl_less_than_3<-bookvalues %>%
  mutate(quantile = ntile(BookValueSum, 10),
         EIN = as.character(EIN)) %>% 
  group_by(EIN) %>% 
  summarise(appear_times = n()) %>% 
  filter(appear_times < 3) %>% 
  inner_join(y=ein_to_name, by = c("EIN")) %>% 
  kbl(caption = "Unusual EIN (appear less than 3 times)") %>%
  kable_material() %>%
  row_spec(row = 0, background = "red", color = "white", bold = TRUE)
EIN_appearl_less_than_3
Unusual EIN (appear less than 3 times)
EIN appear_times BusinessName
161328541 1 THE ROCHESTER CITY BALLET
237003520 2 BALLET OKLAHOMA INC
237424849 1 SAINT LOUIS BALLET COMPANY
260868264 2 PARADOSI CHRISTIAN BALLET
311784286 2 BALLETX
461513558 1 BALLET VERO BEACH INC
510394850 1 FIRST STATE BALLET THEATRE INC
541244590 1 MANASSAS PERFORMING ARTS INC
542124670 2 TERPSICORPS THEATRE OF DANCE INC
616033779 2 KENTUCKY DANCE COUNCIL INC
841622654 1 TEXAS BALLET THEATER INC
931009305 2 OREGON BALLET THEATRE
943197247 1 SMUIN BALLET

Percentage change

changes <- bookvalues %>% 
  group_by(EIN) %>% 
  mutate(change = (BookValueSum - lag(BookValueSum)) / lag(BookValueSum) * 100) %>%
  select(EIN, BusinessName, year, BookValueSum, change) %>% 
  mutate(change_abs = abs(change)) 

#Making an infinite value NA
changes[145, ]$change = NA
all_changes_pct <- ggplot(changes, aes(x = year, y = changes$change, color =  as.factor(BusinessName))) +
  geom_line(na.rm = FALSE, size = 0.2) +
  ylim(-200, 800)+
  theme_bw() +
  theme(legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7))+
  theme(plot.title = element_text(size = 6, face = "bold", hjust = .5),
        axis.title = element_text(size = 4, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 4),
        axis.text.y = element_text(size = 4),
        strip.text = element_text(face="bold",size = 5)) +
  labs(y = "Change in Buildings Book Value Each Year (%)",
       x = "Year",
       title = "% Change in Bookvalues of Building Endowment each Fiscal Year",
       color = "EIN")
## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.
ggplotly(all_changes_pct)
# filter out EIN with normal depreciation change
filtered_changes <- changes %>% 
  filter(change_abs > 10) %>% 
  ggplot(aes(x = year, y = change, color = as.factor(BusinessName))) +
  geom_line(na.rm = FALSE, size = 0.2) +
  ylim(-200, 800)+
  theme_bw() +
  theme(legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7))+
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 10, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10),
        axis.text.y = element_text(size = 6),
        strip.text = element_text(face="bold",size = 5)) +
  labs(y = "Change in Buildings Book Value Each Year (%)",
       x = "Year",
       title = "Change in Bookvalues of Building Endowment each Fiscal Year",
       color = "EIN")


ggplotly(p = filtered_changes)
big_change<-changes %>% 
  filter(change_abs > 10) %>% 
  kbl(caption = "Changes larger than 10%") %>%
  kable_material() %>%
  row_spec(row = 0, background = "red", color = "white", bold = TRUE)
big_change
Changes larger than 10%
EIN BusinessName year BookValueSum change change_abs
942427112 NEVADA BALLET THEATRE INC 2021 5011759 85.28527 85.28527
931009305 OREGON BALLET THEATRE 2017 88534 -95.61477 95.61477
930765746 EUGENE BALLET COMPANY 2020 1790522 168.10202 168.10202
930765746 EUGENE BALLET COMPANY 2022 1102719 -38.41355 38.41355
841150857 ASPEN SANTA FE BALLET 2017 91874 -25.95285 25.95285
841150857 ASPEN SANTA FE BALLET 2018 74547 -18.85953 18.85953
841150857 ASPEN SANTA FE BALLET 2021 53789 -15.11512 15.11512
730667485 TULSA BALLET THEATRE INC 2017 11213840 50.78608 50.78608
730667485 TULSA BALLET THEATRE INC 2018 23021561 105.29596 105.29596
730667485 TULSA BALLET THEATRE INC 2020 10772985 -53.20480 53.20480
630813626 STATE OF ALABAMA BALLET INC 2019 2320424 89.41263 89.41263
630813626 STATE OF ALABAMA BALLET INC 2020 1072333 -53.78720 53.78720
630505056 COMMUNITY BALLET ASSOCIATION INC 2018 339933 -57.10506 57.10506
621018942 BALLET MEMPHIS CORPORATION 2019 14865433 779.42097 779.42097
621018942 BALLET MEMPHIS CORPORATION 2020 28353167 90.73220 90.73220
581891235 BALLETHNIC DANCE COMPANY 2019 131159 -55.24393 55.24393
581047778 ATLANTA BALLET INC 2018 14597136 92.61938 92.61938
581047778 ATLANTA BALLET INC 2020 7266609 -50.21894 50.21894
546049848 THE RICHMOND BALLET 2019 4630690 55.41825 55.41825
546049848 THE RICHMOND BALLET 2020 8865577 91.45261 91.45261
520846173 THE WASHINGTON BALLET 2021 1298710 -48.13539 48.13539
391134735 MILWAUKEE BALLET COMPANY INC 2019 390192 51.33223 51.33223
382026127 GRAND RAPIDS BALLET COMPANY 2017 3697938 -53.82845 53.82845
341645238 VERB BALLETS INC 2018 45230 NA Inf
311784286 BALLETX 2021 0 -100.00000 100.00000
310858562 BALLET METROPOLITAN INC 2018 2054607 -64.93727 64.93727
310858562 BALLET METROPOLITAN INC 2019 3892306 89.44285 89.44285
310858562 BALLET METROPOLITAN INC 2021 1837699 -52.78637 52.78637
237247009 THE CHATTANOOGA BALLET 2020 478976 120.86571 120.86571
237247009 THE CHATTANOOGA BALLET 2021 225913 -52.83417 52.83417
237161084 THE PHILADELPHIA DANCE COMPANY 2017 163240 -11.46449 11.46449
237161084 THE PHILADELPHIA DANCE COMPANY 2018 734364 349.86768 349.86768
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2020 6530935 12.76395 12.76395
210732575 PRINCETON BALLET SOCIETY 2018 590236 -50.45621 50.45621
133307859 THE ARMITAGE FOUNDATION LTD 2017 -43464 -161.03209 161.03209
132685755 BALLET HISPANICO OF NEW YORK INC 2018 16346203 71.13475 71.13475
132685755 BALLET HISPANICO OF NEW YORK INC 2020 6895294 -57.81715 57.81715
132584273 ALVIN AILEY DANCE FOUNDATION INC 2019 44797058 96.96472 96.96472
131882106 BALLET THEATRE FOUNDATION INC 2016 74507 -46.59418 46.59418
131882106 BALLET THEATRE FOUNDATION INC 2017 9502 -87.24684 87.24684
131882106 BALLET THEATRE FOUNDATION INC 2018 0 -100.00000 100.00000

Book Value Change Ranking (%)

ranking<-changes %>%
  arrange(desc(change_abs)) %>% 
  select(EIN, BusinessName, year, change, BookValueSum) %>% 
  kbl(caption = "Ranking of Change in Book Value Per Year  (%)") %>%
  kable_material() %>%
  row_spec(row = 0, background = "red", color = "white", bold = TRUE)
ranking
Ranking of Change in Book Value Per Year (%)
EIN BusinessName year change BookValueSum
341645238 VERB BALLETS INC 2018 NA 45230
621018942 BALLET MEMPHIS CORPORATION 2019 779.4209656 14865433
237161084 THE PHILADELPHIA DANCE COMPANY 2018 349.8676795 734364
930765746 EUGENE BALLET COMPANY 2020 168.1020168 1790522
133307859 THE ARMITAGE FOUNDATION LTD 2017 -161.0320859 -43464
237247009 THE CHATTANOOGA BALLET 2020 120.8657078 478976
730667485 TULSA BALLET THEATRE INC 2018 105.2959646 23021561
311784286 BALLETX 2021 -100.0000000 0
131882106 BALLET THEATRE FOUNDATION INC 2018 -100.0000000 0
132584273 ALVIN AILEY DANCE FOUNDATION INC 2019 96.9647238 44797058
931009305 OREGON BALLET THEATRE 2017 -95.6147711 88534
581047778 ATLANTA BALLET INC 2018 92.6193828 14597136
546049848 THE RICHMOND BALLET 2020 91.4526129 8865577
621018942 BALLET MEMPHIS CORPORATION 2020 90.7321973 28353167
310858562 BALLET METROPOLITAN INC 2019 89.4428472 3892306
630813626 STATE OF ALABAMA BALLET INC 2019 89.4126261 2320424
131882106 BALLET THEATRE FOUNDATION INC 2017 -87.2468359 9502
942427112 NEVADA BALLET THEATRE INC 2021 85.2852687 5011759
132685755 BALLET HISPANICO OF NEW YORK INC 2018 71.1347540 16346203
310858562 BALLET METROPOLITAN INC 2018 -64.9372684 2054607
132685755 BALLET HISPANICO OF NEW YORK INC 2020 -57.8171518 6895294
630505056 COMMUNITY BALLET ASSOCIATION INC 2018 -57.1050553 339933
546049848 THE RICHMOND BALLET 2019 55.4182545 4630690
581891235 BALLETHNIC DANCE COMPANY 2019 -55.2439320 131159
382026127 GRAND RAPIDS BALLET COMPANY 2017 -53.8284510 3697938
630813626 STATE OF ALABAMA BALLET INC 2020 -53.7871958 1072333
730667485 TULSA BALLET THEATRE INC 2020 -53.2048022 10772985
237247009 THE CHATTANOOGA BALLET 2021 -52.8341712 225913
310858562 BALLET METROPOLITAN INC 2021 -52.7863688 1837699
391134735 MILWAUKEE BALLET COMPANY INC 2019 51.3322319 390192
730667485 TULSA BALLET THEATRE INC 2017 50.7860781 11213840
210732575 PRINCETON BALLET SOCIETY 2018 -50.4562082 590236
581047778 ATLANTA BALLET INC 2020 -50.2189402 7266609
520846173 THE WASHINGTON BALLET 2021 -48.1353925 1298710
131882106 BALLET THEATRE FOUNDATION INC 2016 -46.5941754 74507
930765746 EUGENE BALLET COMPANY 2022 -38.4135464 1102719
841150857 ASPEN SANTA FE BALLET 2017 -25.9528511 91874
841150857 ASPEN SANTA FE BALLET 2018 -18.8595250 74547
841150857 ASPEN SANTA FE BALLET 2021 -15.1151230 53789
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2020 12.7639494 6530935
237161084 THE PHILADELPHIA DANCE COMPANY 2017 -11.4644914 163240
640732185 BALLET MAGNIFICAT INC 2020 -8.7801215 223496
640732185 BALLET MAGNIFICAT INC 2019 -8.1465708 245008
841150857 ASPEN SANTA FE BALLET 2019 -8.1425141 68477
941415298 SAN FRANCISCO BALLET ASSOCIATION 2021 -7.6340157 13212979
841150857 ASPEN SANTA FE BALLET 2020 -7.4623596 63367
132642091 DANCE THEATRE OF HARLEM INC 2019 7.0547559 4751256
391134735 MILWAUKEE BALLET COMPANY INC 2018 -6.8524526 257838
640732185 BALLET MAGNIFICAT INC 2016 -6.7967642 300367
941415298 SAN FRANCISCO BALLET ASSOCIATION 2020 -6.7398261 14305027
210732575 PRINCETON BALLET SOCIETY 2019 -6.5587324 551524
746060386 BALLET AUSTIN INCORPORATED 2021 -6.3650884 3648651
382026127 GRAND RAPIDS BALLET COMPANY 2021 -6.3552062 3088395
132642091 DANCE THEATRE OF HARLEM INC 2020 6.3416705 5052565
640732185 BALLET MAGNIFICAT INC 2017 -6.3109463 281411
941415298 SAN FRANCISCO BALLET ASSOCIATION 2019 -6.2849385 15338838
341645238 VERB BALLETS INC 2019 -5.9208490 42552
382026127 GRAND RAPIDS BALLET COMPANY 2018 -5.5966866 3490976
382026127 GRAND RAPIDS BALLET COMPANY 2019 -5.5281675 3297989
520846173 THE WASHINGTON BALLET 2019 -5.5184196 2569933
132642091 DANCE THEATRE OF HARLEM INC 2017 -5.4555107 4632021
581440788 NASHVILLE BALLET 2018 -5.4386347 6879884
746060386 BALLET AUSTIN INCORPORATED 2019 -5.2847655 4071036
621018942 BALLET MEMPHIS CORPORATION 2018 -5.2841141 1690366
640732185 BALLET MAGNIFICAT INC 2018 -5.2140819 266738
621018942 BALLET MEMPHIS CORPORATION 2017 -5.0396300 1784670
941415298 SAN FRANCISCO BALLET ASSOCIATION 2018 -5.0171961 16367527
581314711 CHARLOTTE BALLET 2021 -5.0104017 5154185
581891235 BALLETHNIC DANCE COMPANY 2021 -4.9171280 118865
581314711 CHARLOTTE BALLET 2020 -4.8970704 5426052
520846173 THE WASHINGTON BALLET 2017 -4.7864564 2852613
581891235 BALLETHNIC DANCE COMPANY 2020 -4.6866780 125012
520846173 THE WASHINGTON BALLET 2018 -4.6475635 2720036
581314711 CHARLOTTE BALLET 2019 -4.5891891 5705452
746060386 BALLET AUSTIN INCORPORATED 2018 -4.5749439 4298185
581314711 CHARLOTTE BALLET 2018 -4.4554225 5979880
942427112 NEVADA BALLET THEATRE INC 2019 -4.4546996 2819259
210732575 PRINCETON BALLET SOCIETY 2021 -4.3481783 507717
746060386 BALLET AUSTIN INCORPORATED 2020 -4.2828902 3896678
581314711 CHARLOTTE BALLET 2017 -4.2679736 6258733
942427112 NEVADA BALLET THEATRE INC 2018 -4.1886209 2950704
132642091 DANCE THEATRE OF HARLEM INC 2018 -4.1853437 4438155
730667485 TULSA BALLET THEATRE INC 2021 -4.1599334 10324836
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2019 4.1576844 5791687
581420599 THE GEORGIA BALLET INC 2020 -4.1323460 753491
942427112 NEVADA BALLET THEATRE INC 2020 -4.0567752 2704888
581420599 THE GEORGIA BALLET INC 2019 -3.8952051 785970
581420599 THE GEORGIA BALLET INC 2018 -3.8693191 817826
746060386 BALLET AUSTIN INCORPORATED 2017 -3.8478559 4504252
942427112 NEVADA BALLET THEATRE INC 2017 -3.8418895 3079701
210732575 PRINCETON BALLET SOCIETY 2020 -3.7581320 530797
581420599 THE GEORGIA BALLET INC 2017 -3.7251800 850744
581440788 NASHVILLE BALLET 2020 -3.6814935 6391306
132685755 BALLET HISPANICO OF NEW YORK INC 2022 -3.6361434 6411442
581440788 NASHVILLE BALLET 2017 -3.6264451 7275576
237247009 THE CHATTANOOGA BALLET 2019 -3.6198713 216863
132584273 ALVIN AILEY DANCE FOUNDATION INC 2018 -3.5622325 22743696
581440788 NASHVILLE BALLET 2019 -3.5507721 6635595
132685755 BALLET HISPANICO OF NEW YORK INC 2021 -3.5085669 6653368
237247009 THE CHATTANOOGA BALLET 2018 -3.4938281 225008
132584273 ALVIN AILEY DANCE FOUNDATION INC 2021 -3.4829178 42044504
132584273 ALVIN AILEY DANCE FOUNDATION INC 2017 -3.4309801 23583806
364009741 THE JOFFREY BALLET 2021 -3.3898303 15281758
237247009 THE CHATTANOOGA BALLET 2017 -3.3754802 233154
630813626 STATE OF ALABAMA BALLET INC 2018 -3.3423674 1225063
741394920 HOUSTON BALLET FOUNDATION 2021 -3.3348431 29194686
930765746 EUGENE BALLET COMPANY 2019 -3.2812075 667851
364009741 THE JOFFREY BALLET 2020 -3.2786942 15817960
741394920 HOUSTON BALLET FOUNDATION 2020 -3.2272203 30201871
930765746 EUGENE BALLET COMPANY 2018 -3.1768289 690508
364009741 THE JOFFREY BALLET 2019 -3.1746028 16354163
741394920 HOUSTON BALLET FOUNDATION 2018 -3.1665999 32190211
581047778 ATLANTA BALLET INC 2021 -3.1537681 7037437
930765746 EUGENE BALLET COMPANY 2017 -3.0790139 713164
364009741 THE JOFFREY BALLET 2018 -3.0769227 16890365
846038137 COLORADO BALLET 2021 -3.0710961 5047657
741394920 HOUSTON BALLET FOUNDATION 2019 -3.0479918 31209056
364009741 THE JOFFREY BALLET 2017 -2.9850743 17426567
581047778 ATLANTA BALLET INC 2017 -2.9602632 7578228
941415298 SAN FRANCISCO BALLET ASSOCIATION 2017 -2.8941246 17232095
741394920 HOUSTON BALLET FOUNDATION 2017 -2.7773212 33242880
132584273 ALVIN AILEY DANCE FOUNDATION INC 2020 -2.7576253 43561723
846038137 COLORADO BALLET 2018 -2.7139960 5475088
616033779 KENTUCKY DANCE COUNCIL INC 2021 2.7106348 995302
846038137 COLORADO BALLET 2019 -2.5831183 5333660
520846173 THE WASHINGTON BALLET 2020 -2.5640357 2504039
846038137 COLORADO BALLET 2020 -2.3637240 5207587
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2018 -2.3414521 5560499
436052680 KANSAS CITY BALLET ASSOCIATION 2020 -2.3189331 22215144
436052680 KANSAS CITY BALLET ASSOCIATION 2019 -2.2663815 22742528
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2017 -2.2634111 5693817
391134735 MILWAUKEE BALLET COMPANY INC 2017 -2.2070072 276806
846038137 COLORADO BALLET 2017 -2.0884780 5627827
132685755 BALLET HISPANICO OF NEW YORK INC 2017 -2.0245300 9551656
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2021 -1.6608801 6422464
581440788 NASHVILLE BALLET 2016 -1.0024919 7549349
630505056 COMMUNITY BALLET ASSOCIATION INC 2017 -0.9656276 792478
132642091 DANCE THEATRE OF HARLEM INC 2021 0.9598887 5101064
237161084 THE PHILADELPHIA DANCE COMPANY 2019 0.0000000 734364
237161084 THE PHILADELPHIA DANCE COMPANY 2020 0.0000000 734364
237161084 THE PHILADELPHIA DANCE COMPANY 2021 0.0000000 734364
237003520 BALLET OKLAHOMA INC 2021 0.0000000 3222367
133307859 THE ARMITAGE FOUNDATION LTD 2018 0.0000000 -43464
133307859 THE ARMITAGE FOUNDATION LTD 2019 0.0000000 -43464
133307859 THE ARMITAGE FOUNDATION LTD 2020 0.0000000 -43464
133307859 THE ARMITAGE FOUNDATION LTD 2021 0.0000000 -43464
943197247 SMUIN BALLET 2021 NA 3246223
942427112 NEVADA BALLET THEATRE INC 2016 NA 3202747
941415298 SAN FRANCISCO BALLET ASSOCIATION 2016 NA 17745677
931009305 OREGON BALLET THEATRE 2016 NA 2018914
930765746 EUGENE BALLET COMPANY 2016 NA 735820
910897129 PACIFIC NORTHWEST BALLET ASSOCIATION 2016 NA 0
910897129 PACIFIC NORTHWEST BALLET ASSOCIATION 2017 NaN 0
910897129 PACIFIC NORTHWEST BALLET ASSOCIATION 2018 NaN 0
910897129 PACIFIC NORTHWEST BALLET ASSOCIATION 2019 NaN 0
910897129 PACIFIC NORTHWEST BALLET ASSOCIATION 2020 NaN 0
910897129 PACIFIC NORTHWEST BALLET ASSOCIATION 2021 NaN 0
846038137 COLORADO BALLET 2016 NA 5747870
841622654 TEXAS BALLET THEATER INC 2021 NA 1644891
841150857 ASPEN SANTA FE BALLET 2016 NA 124075
746060386 BALLET AUSTIN INCORPORATED 2016 NA 4684505
741394920 HOUSTON BALLET FOUNDATION 2016 NA 34192516
730667485 TULSA BALLET THEATRE INC 2016 NA 7436920
640732185 BALLET MAGNIFICAT INC 2015 NA 322271
630813626 STATE OF ALABAMA BALLET INC 2017 NA 1267425
630505056 COMMUNITY BALLET ASSOCIATION INC 2016 NA 800205
621018942 BALLET MEMPHIS CORPORATION 2015 NA 1879384
616033779 KENTUCKY DANCE COUNCIL INC 2020 NA 969035
581891235 BALLETHNIC DANCE COMPANY 2017 NA 293053
581440788 NASHVILLE BALLET 2015 NA 7625797
581420599 THE GEORGIA BALLET INC 2016 NA 883662
581314711 CHARLOTTE BALLET 2016 NA 6537763
581047778 ATLANTA BALLET INC 2015 NA 7809407
546049848 THE RICHMOND BALLET 2018 NA 2979502
542124670 TERPSICORPS THEATRE OF DANCE INC 2020 NA 0
542124670 TERPSICORPS THEATRE OF DANCE INC 2021 NaN 0
541244590 MANASSAS PERFORMING ARTS INC 2020 NA 0
520846173 THE WASHINGTON BALLET 2016 NA 2996016
510394850 FIRST STATE BALLET THEATRE INC 2016 NA 40525
461513558 BALLET VERO BEACH INC 2021 NA 0
455395602 BALLET FIVE EIGHT NFP 2016 NA 0
455395602 BALLET FIVE EIGHT NFP 2018 NaN 0
455395602 BALLET FIVE EIGHT NFP 2019 NaN 0
455395602 BALLET FIVE EIGHT NFP 2020 NaN 0
454864757 NGOMA CENTER FOR DANCE 2019 NA 0
454864757 NGOMA CENTER FOR DANCE 2020 NaN 0
454864757 NGOMA CENTER FOR DANCE 2021 NaN 0
436052680 KANSAS CITY BALLET ASSOCIATION 2018 NA 23269913
391134735 MILWAUKEE BALLET COMPANY INC 2016 NA 283053
382026127 GRAND RAPIDS BALLET COMPANY 2016 NA 8009127
364009741 THE JOFFREY BALLET 2016 NA 17962769
341645238 VERB BALLETS INC 2016 NA 0
341645238 VERB BALLETS INC 2017 NaN 0
311784286 BALLETX 2019 NA 327
310858562 BALLET METROPOLITAN INC 2016 NA 5859803
260868264 PARADOSI CHRISTIAN BALLET 2017 NA 0
260868264 PARADOSI CHRISTIAN BALLET 2018 NaN 0
237424849 SAINT LOUIS BALLET COMPANY 2020 NA 0
237247009 THE CHATTANOOGA BALLET 2016 NA 241299
237161084 THE PHILADELPHIA DANCE COMPANY 2016 NA 184378
237003520 BALLET OKLAHOMA INC 2020 NA 3222367
231629970 THE PENNSYLVANIA BALLET ASSOCIATION 2016 NA 5825676
210732575 PRINCETON BALLET SOCIETY 2016 NA 1191342
161328541 THE ROCHESTER CITY BALLET 2016 NA 1140339
133307859 THE ARMITAGE FOUNDATION LTD 2016 NA 71215
132685755 BALLET HISPANICO OF NEW YORK INC 2016 NA 9749028
132642091 DANCE THEATRE OF HARLEM INC 2016 NA 4899303
132584273 ALVIN AILEY DANCE FOUNDATION INC 2016 NA 24421710
131882106 BALLET THEATRE FOUNDATION INC 2015 NA 139511
131882106 BALLET THEATRE FOUNDATION INC 2019 NaN 0
131882106 BALLET THEATRE FOUNDATION INC 2020 NaN 0
131882106 BALLET THEATRE FOUNDATION INC 2021 NaN 0 
change_less_than_10<-changes %>% 
  filter(change_abs <=10)

ggplotly(
  ggplot(change_less_than_10, aes(x = change_abs)) + 
  geom_histogram()+
  theme_bw()
  )
## `stat_bin()` using `bins = 30`. Pick better value with `binwidth`.
change_larger_than_10 <- changes %>% 
  filter(change_abs > 10)

ggplotly(
  ggplot(change_larger_than_10, aes(x = change_abs)) + 
  geom_histogram()+
  theme_bw()
  )
## `stat_bin()` using `bins = 30`. Pick better value with `binwidth`.
## Warning: Removed 1 rows containing non-finite values (`stat_bin()`).
changes %>% 
  drop_na() %>% 
  group_by(year) %>% 
  summarise(mean = mean(change),
            median = median(change),
            max = max(change),
            min = min(change)) %>% 
  kbl(caption = "Summary statistics of Building Book Value Changes (Fiscal Year)") %>%
  kable_material() %>%
  row_spec(row = 0, background = "red", color = "white", bold = TRUE)
Summary statistics of Building Book Value Changes (Fiscal Year)
year mean median max min
2016 -18.131144 -6.796764 -1.002492 -46.59418
2017 -16.296664 -3.675813 50.786078 -161.03209
2018 8.112037 -4.186982 349.867680 -100.00000
2019 35.590075 -3.281207 779.420966 -55.24393
2020 7.365791 -3.480094 168.102017 -57.81715
2021 -9.612285 -3.508567 85.285269 -100.00000
2022 -21.024845 -21.024845 -3.636143 -38.41355 
unique(bookvalues$EIN)
##  [1] "943197247" "942427112" "941415298" "931009305" "930765746" "910897129"
##  [7] "846038137" "841622654" "841150857" "746060386" "741394920" "730667485"
## [13] "640732185" "630813626" "630505056" "621018942" "616033779" "581891235"
## [19] "581440788" "581420599" "581314711" "581047778" "546049848" "542124670"
## [25] "541244590" "520846173" "510394850" "461513558" "455395602" "454864757"
## [31] "436052680" "391134735" "382026127" "364009741" "341645238" "311784286"
## [37] "310858562" "260868264" "237424849" "237247009" "237161084" "237003520"
## [43] "231629970" "210732575" "161328541" "133307859" "132685755" "132642091"
## [49] "132584273" "131882106"